﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using vudu.Logica.Clases;
using System.Data;
using System.Data.SqlClient;

namespace vudu.EnlaceDatos.SqlServer
{
    public class SqlServerHorarioPreMatricula
    {

        #region Agrega PreMatricula

        public bool AgregarPreMatricula(int miEstudiante, HorarioPreMatricula miHorario)
        {
            // Este procedimiento recibe el id del estudiante y el horario seleccionado por el mismo
            //para poder agregarlo a la base de datos, se crea una Prematricula y luego se manda a 
            //insertar las materias seleccionadas.
            // Las verificaciones validan los inserts que de no ser realizados los inserts no se realizan,
            //Varificacion1 valida la creacion de la prematricula y verificacion2 valida el inserts de las materias seleccionadas.
            
            bool verificacion;
            bool verificacion2;
            Int32 MaxHorarioPreMatricula;
            verificacion = InsertarHorarioPreMatricula(miEstudiante, miHorario);

            if (verificacion)
            {
                MaxHorarioPreMatricula = TraerUltimoIdHorarioPreMatricula(miEstudiante);
                verificacion2 = InsertarEstudianteHorario(MaxHorarioPreMatricula, miHorario);

                if (verificacion2)
                    return true;
                else
                    return false;
            }
            else
                return false;

        }

        public bool InsertarHorarioPreMatricula(int miEstudiante, HorarioPreMatricula miHorario)
        {
            // Dado el horario y el estudiante se procede a insertar en la base de datos la prematricula
            //y las secciones que se van a ver en ese semestre con la fecha del dia en que se realiza la operacion.
            
            Conexion miConexion = new Conexion();
            SqlCommand command = new SqlCommand();
            bool retorno = true;

            try
            {

                //Se abre la conexion a base de datos
                miConexion.AbrirConexionBd();
                int day = miHorario.Fecha.Day;
                int Mes = miHorario.Fecha.Month;
                int Anio = miHorario.Fecha.Year;
                String fecha = Anio + "-" + Mes + "-" + day;
                //Se introducen los datos de la conexion
                command.Connection = miConexion.ObjetoConexion();
                command.CommandType = System.Data.CommandType.Text;
                command.CommandText = "INSERT INTO HorarioPreMatricula (estatus,nombre, fecha, fk_Estudiante) VALUES ('" + miHorario.Estatus + "', '" + miHorario.Nombre + "', '" + fecha + "', " + miEstudiante + ")";
                command.CommandTimeout = 10;

                //Ejecuto el store procedure
                command.ExecuteReader();
            }

            catch (SqlException)
            {
                retorno = false;
            }

            catch (Exception)
            {
                retorno = false;
            }

            finally
            {
                miConexion.CerrarConexionBd();
            }

            return retorno;
        }

        public Int32 TraerUltimoIdHorarioPreMatricula(int miEstudiante)
        {
            // Esta funcion se encarga de obtener el ultimo id de las PreMatricula creada para poder
            //hacer los inserts de las secciones que pertenecen a esa prematricula.
            Conexion miConexion = new Conexion();
            SqlCommand command = new SqlCommand();
            SqlDataReader reader = null;
            Int32 retorno = 0;

            try
            {
                //Se abre la conexion a base de datos
                miConexion.AbrirConexionBd();

                //Se intoducen los datos de la conexion
                command.Connection = miConexion.ObjetoConexion();
                command.CommandType = System.Data.CommandType.Text;
                command.CommandText = "SELECT MAX(id_HorarioPreMatricula) as Maximo FROM HorarioPreMatricula WHERE fk_Estudiante = " + miEstudiante;
                command.CommandTimeout = 10;

                //Ejecuto el commando

                reader = command.ExecuteReader();

                while (reader.Read())

                    //Capturo el id de la prematricula agregada
                    retorno = reader.GetInt32(0);

                return retorno;
            }

            catch (SqlException)
            {
                return 0;
            }

            catch (Exception)
            {
                return 0;
            }

            finally
            {
                if (reader != null)
                    reader.Close();
                miConexion.CerrarConexionBd();
            }

        }

        public bool InsertarEstudianteHorario(int MaxHorarioPreMatricula, HorarioPreMatricula miHorario)
        {
            // Se recibe el id de la Prematricula creada y el horario que contiene todas las secciones seleccionadas
            //por el estudiante, y se procede a realizar los inserts correspondientes de la prematricula con respecto
            //a las secciones elegidas.


            bool retorno = true;

            try
            {

                //Se abre la conexion a base de datos

                foreach (SeccionMateria miSeccion in miHorario.ListaSecciones)
                {
                    Conexion miConexion = new Conexion();
                    SqlCommand command = new SqlCommand();

                    miConexion.AbrirConexionBd();
                    //Se introducen los datos de la conexion
                    command.Connection = miConexion.ObjetoConexion();
                    command.CommandType = System.Data.CommandType.Text;
                    if (miSeccion.ListaPractica.Count == 0)
                        command.CommandText = "INSERT INTO EstudianteHorario (fk_HorarioPreMatricula, fk_Practica,fk_SeccionMateria) VALUES (" + MaxHorarioPreMatricula + ", 0," + miSeccion.IdSeccionMateria + ")";
                    else
                        command.CommandText = "INSERT INTO EstudianteHorario (fk_HorarioPreMatricula, fk_Practica,fk_SeccionMateria) VALUES (" + MaxHorarioPreMatricula + ", " + miSeccion.ListaPractica[0].IdPractica + ", " + miSeccion.IdSeccionMateria + ")";
                    command.CommandTimeout = 10;

                    //Ejecuto el store procedure
                    command.ExecuteReader();
                    miConexion.CerrarConexionBd();
                }

            }
            catch (SqlException)
            {
                retorno = false;
            }

            catch (Exception)
            {
                retorno = false;
            }



            return retorno;
        }

        #endregion

        #region Cargar Horarios dado un estudiante

        public List<HorarioPreMatricula> CargarMisHorarios(int miEstudiante)
        {
            // Dado un estudiante, esta funcion se encarga de obtener todos los horarios del estudiante
            // con toda su informacion respectiva, sea lista de prematriculas (Historial o pendientes)
            //con sus respectivas secciones, horarios y materias.
            //Dado que esta Funcion llama a funciones que a su ves llaman a otras funciones, dado a que
            //cada funcion cierra la conexion con la base de datos, se procede a realizar en el reader
            //el cargado de los id de las prematriculas en una lista de enteros, y luego se se recorre
            // esa lista para el cargado de la base de datos
           
            Conexion miConexion = new Conexion();
            SqlCommand command = new SqlCommand();
            List<HorarioPreMatricula> retorno = new List<HorarioPreMatricula>();
            SqlDataReader reader = null;
            List<int> listaGenerica = new List<int>();

            try
            {

                //Se abre la conexion a base de datos
                miConexion.AbrirConexionBd();

                //Se introducen los datos de la conexion
                command.Connection = miConexion.ObjetoConexion();
                command.CommandType = System.Data.CommandType.Text;
                command.CommandText = "SELECT H.id_HorarioPreMatricula as prematricual,H.estatus as estatus,H.fecha as fecha,H.nombre as nombre FROM HorarioPreMatricula H WHERE H.fk_Estudiante = " + miEstudiante;
                command.CommandTimeout = 10;

                reader = command.ExecuteReader();

                while (reader.Read())
                {

                    HorarioPreMatricula miHorario = new HorarioPreMatricula();
                    miHorario.Codigo = reader.GetInt32(0);
                    miHorario.Estatus = reader["estatus"].ToString();
                    miHorario.Fecha = reader.GetDateTime(2);
                    miHorario.Nombre = reader["nombre"].ToString();
                    listaGenerica.Add(reader.GetInt32(0));
                    retorno.Add(miHorario);
                }

                for (int contador = 0; contador < listaGenerica.Count(); contador++)
                {
                    retorno[contador].ListaSecciones = BuscarListaSeccionMateria(listaGenerica[contador]);
                }

                return retorno;

            }
            catch (SqlException)
            {
                return null;
            }

            catch (Exception)
            {
                return null;
            }

            finally
            {
                if (reader != null)
                    reader.Close();
                miConexion.CerrarConexionBd();
            }
        }

        public List<SeccionMateria> BuscarListaSeccionMateria(int idLista)
        {
            // Dado el id de la Prematricula se procede a rellenar la lista de seccion materias que 
            //pertenecen a la prematricula
            // Aca el recorrido del reader es similar a la de la funcion anterior,pero se procede a guardar
            //dos id distintos que son los de practica y los de seccionmateria para evitar el error del
            //cerrado de conexion.
            List<SeccionMateria> retorno = new List<SeccionMateria>();
            SqlDataReader reader = null;
            Conexion miConexion = new Conexion();
            SqlCommand command = new SqlCommand();
            List<int> listaGenericaMateria = new List<int>();
            List<int> listaGenericaPractica = new List<int>();

            try
            {

                //Se abre la conexion a base de datos
                miConexion.AbrirConexionBd();

                //Se introducen los datos de la conexion
                command.Connection = miConexion.ObjetoConexion();
                command.CommandType = System.Data.CommandType.Text;
                command.CommandText = "SELECT fk_SeccionMateria, fk_Practica FROM EstudianteHorario WHERE  fk_HorarioPreMatricula = " + idLista;
                command.CommandTimeout = 10;

                reader = command.ExecuteReader();

                while (reader.Read())
                {
                    listaGenericaMateria.Add(reader.GetInt32(0));
                    listaGenericaPractica.Add(reader.GetInt32(1));
                }

                for (int contador = 0; contador < listaGenericaMateria.Count(); contador++)
                {

                    SeccionMateria miSeccion = new SeccionMateria();
                    miSeccion = BuscarSeccionMateria(listaGenericaMateria[contador]);

                    if (listaGenericaPractica[contador] != 0)
                        miSeccion.ListaPractica.Add(BuscarPractica(listaGenericaPractica[contador]));
                    else
                        miSeccion.ListaPractica = null;

                    retorno.Add(miSeccion);
                }

                return retorno;

            }
            catch (SqlException)
            {
                return null;
            }

            catch (Exception)
            {
                return null;
            }

            finally
            {
                if (reader != null)
                    reader.Close();
                miConexion.CerrarConexionBd();
            }
        }

        public SeccionMateria BuscarSeccionMateria(int idSeccionMateria)
        {
            //Esta funcion se encarga de buscar los datos de la base de de una seccion materia,
            //es decir si es una practica o si es una materia para poder hacer los respectivos 
            //llamados a las funciones y poder asignar los valores.
            SeccionMateria retorno = new SeccionMateria();
            SqlDataReader reader = null;
            Conexion miConexion = new Conexion();
            SqlCommand command = new SqlCommand();

            try
            {
                //Se abre la conexion a base de datos
                miConexion.AbrirConexionBd();

                //Se introducen los datos de la conexion
                command.Connection = miConexion.ObjetoConexion();
                command.CommandType = System.Data.CommandType.Text;
                command.CommandText = "SELECT numeroSeccion as seccion,fk_Profesor as idprofesor,fk_Materia  as idmateria, id_Seccion FROM SeccionMateria WHERE id_Seccion = " + idSeccionMateria;
                command.CommandTimeout = 10;

                int cedula = 0;
                int materia = 0;

                reader = command.ExecuteReader();

                while (reader.Read())
                {
                    retorno.Codigo = reader.GetInt32(0);
                    cedula = reader.GetInt32(1);
                    materia = reader.GetInt32(2);
                    retorno.IdSeccionMateria = reader.GetInt32(3);
                }

                retorno.Profesor = BuscarProfesor(cedula);
                retorno.Materia = BuscarMateria(materia);
                retorno.HorasClase = BuscarListaHorariosClaseMateria(idSeccionMateria);

                return retorno;

            }
            catch (SqlException)
            {
                return null;
            }

            catch (Exception)
            {
                return null;
            }

            finally
            {
                if (reader != null)
                    reader.Close();
                miConexion.CerrarConexionBd();
            }
        }

        public Profesor BuscarProfesor(int idProfesor)
        {
            //Dado el id del profesor se busca en la base de datos y se obtienen los datos del mismo
            //para poder guardarlos en los objetos.
            Profesor retorno = new Profesor();
            SqlDataReader reader = null;
            Conexion miConexion = new Conexion();
            SqlCommand command = new SqlCommand();
            try
            {

                //Se abre la conexion a base de datos
                miConexion.AbrirConexionBd();

                //Se introducen los datos de la conexion
                command.Connection = miConexion.ObjetoConexion();
                command.CommandType = System.Data.CommandType.Text;
                command.CommandText = "SELECT nombreProfesor as nombre,apellidoProfesor as apellido FROM Profesor WHERE id_Cedula = " + idProfesor;
                command.CommandTimeout = 10;

                reader = command.ExecuteReader();

                while (reader.Read())
                {
                    retorno.Nombre = reader["nombre"].ToString();
                    retorno.Apellido = reader["apellido"].ToString();
                    retorno.Cedula = idProfesor;
                }

                return retorno;

            }
            catch (SqlException)
            {
                return null;
            }

            catch (Exception)
            {
                return null;
            }

            finally
            {
                if (reader != null)
                    reader.Close();
                miConexion.CerrarConexionBd();
            }
        }

        public Materia BuscarMateria(int idMateria)
        {
            //Dado el id de la materia se busca en la base de datos y se obtienen los datos de la misma
            //para poder guardarlos en los objetos.
            Materia retorno = new Materia();
            SqlDataReader reader = null;
            Conexion miConexion = new Conexion();
            SqlCommand command = new SqlCommand();

            try
            {

                //Se abre la conexion a base de datos
                miConexion.AbrirConexionBd();

                //Se introducen los datos de la conexion
                command.Connection = miConexion.ObjetoConexion();
                command.CommandType = System.Data.CommandType.Text;
                command.CommandText = "SELECT nombreMateria as nombre FROM Materia WHERE id_Materia = " + idMateria;
                command.CommandTimeout = 10;

                reader = command.ExecuteReader();

                while (reader.Read())
                    retorno.Nombre = reader["nombre"].ToString();
                retorno.Codigo = idMateria;

                return retorno;

            }
            catch (SqlException)
            {
                return null;
            }

            catch (Exception)
            {
                return null;
            }

            finally
            {
                if (reader != null)
                    reader.Close();
                miConexion.CerrarConexionBd();
            }

        }

        public List<HorarioDeClase> BuscarListaHorariosClaseMateria(int idSeccionMateria)
        {
            //Cada SeccionMateria posee una lista de horarios el cual se obtienene con el idSeccionMateria,
            //esta lista es reccorrida de la misma manera que en funciones anteriores usando un While y guardando
            //la lista de enteros y luego un for recorriendo la lista.
            //En este procedeimiento se verifica si la seccionMateria se trata de una practica o de una materia,
            //en este caso es una MATERIA
            List<HorarioDeClase> retorno = new List<HorarioDeClase>();
            SqlDataReader reader = null;
            Conexion miConexion = new Conexion();
            SqlCommand command = new SqlCommand();

            try
            {

                //Se abre la conexion a base de datos
                miConexion.AbrirConexionBd();

                //Se introducen los datos de la conexion
                command.Connection = miConexion.ObjetoConexion();
                command.CommandType = System.Data.CommandType.Text;
                command.CommandText = "SELECT  diaSemana, horaInicio as horaIni,horaFin as horaFin, fk_Salon, id_Horario FROM HorarioClase WHERE fk_SeccionMateria = " + idSeccionMateria + " AND fk_SeccionPractica is NULL";
                command.CommandTimeout = 10;

                reader = command.ExecuteReader();

                List<int> idSalones = new List<int>();

                while (reader.Read())
                {

                    HorarioDeClase horario = new HorarioDeClase();

                    horario.Dia = reader["diaSemana"].ToString();
                    horario.HoraInicio = reader.GetDateTime(1);
                    horario.HoraFin = reader.GetDateTime(2);
                    idSalones.Add(reader.GetInt32(3));
                    horario.IdHorarioClase = reader.GetInt32(4);

                    retorno.Add(horario);
                }

                for (int contador = 0; contador < idSalones.Count; contador++)
                {
                    retorno[contador].SalonDeClase = BuscarSalon(idSalones[contador]);
                }

                return retorno;

            }
            catch (SqlException)
            {
                return null;
            }

            catch (Exception)
            {
                return null;
            }

            finally
            {
                if (reader != null)
                    reader.Close();
                miConexion.CerrarConexionBd();
            }


        }

        public Practica BuscarPractica(int idPractica)
        {
            //Dado el id de la practica se busca en la base de datos y se obtienen los datos de la misma
            //para poder guardarlos en los objetos.
            Practica retorno = new Practica();

            Conexion miConexion = new Conexion();
            SqlCommand command = new SqlCommand();
            SqlDataReader reader = null;

            try
            {

                //Se abre la conexion a base de datos
                miConexion.AbrirConexionBd();

                //Se introducen los datos de la conexion
                command.Connection = miConexion.ObjetoConexion();
                command.CommandType = System.Data.CommandType.Text;
                command.CommandText = "SELECT numeroSeccion, nombrePractica, fk_Profesor FROM Practica WHERE id_Practica = " + idPractica;
                command.CommandTimeout = 10;

                reader = command.ExecuteReader();

                int idProfesor = 0;

                while (reader.Read())
                {
                    retorno.NumeroSeccion = reader.GetInt32(0);
                    retorno.NombrePractica = reader["nombrePractica"].ToString();
                    idProfesor = reader.GetInt32(2);

                }

                retorno.IdPractica = idPractica;
                retorno.horarioDeClase = BuscarListaHorariosClasePractica(idPractica);
                if (idProfesor !=0)
                {

                    retorno.profesor = BuscarProfesor(idProfesor);

                }

                return retorno;

            }
            catch (SqlException)
            {
                return null;
            }

            catch (Exception)
            {
                return null;
            }

            finally
            {
                if (reader != null)
                    reader.Close();
                miConexion.CerrarConexionBd();
            }
        }

        public List<HorarioDeClase> BuscarListaHorariosClasePractica(int idPractica)
        {
            //Cada SeccionMateria posee una lista de horarios el cual se obtienene con el idSeccionMateria,
            //esta lista es reccorrida de la misma manera que en funciones anteriores usando un While y guardando
            //la lista de enteros y luego un for recorriendo la lista.
            //En este procedeimiento se verifica si la seccionMateria se trata de una practica o de una materia,
            //en este caso es una PRACTICA

            Conexion miConexion = new Conexion();
            SqlCommand command = new SqlCommand();
            SqlDataReader reader1 = null;

            try
            {

                //Se abre la conexion a base de datos
                miConexion.AbrirConexionBd();

                //Se introducen los datos de la conexion
                command.Connection = miConexion.ObjetoConexion();
                command.CommandType = System.Data.CommandType.Text;
                command.CommandText = "SELECT  diaSemana,horaInicio as horaIni,horaFin as horaFin, fk_Salon, id_Horario FROM HorarioClase WHERE fk_SeccionPractica = " + idPractica + " AND fk_SeccionMateria is NULL"; command.CommandTimeout = 10;

                List<HorarioDeClase> retorno = new List<HorarioDeClase>();
                List<int> idSalones = new List<int>();

                reader1 = command.ExecuteReader();

                while (reader1.Read())
                {

                    HorarioDeClase horario = new HorarioDeClase();

                    horario.Dia = reader1["diaSemana"].ToString();
                    horario.HoraInicio = reader1.GetDateTime(1);
                    horario.HoraFin = reader1.GetDateTime(2);
                    idSalones.Add(reader1.GetInt32(3));
                    horario.IdHorarioClase = reader1.GetInt32(4);

                    retorno.Add(horario);
                }

                for (int contador = 0; contador < idSalones.Count; contador++ )
                {
                    retorno[contador].SalonDeClase = BuscarSalon(idSalones[contador]);
                }

                return retorno;

            }
            catch (SqlException)
            {
                return null;
            }

            catch (Exception)
            {
                return null;
            }

            finally
            {
                if (reader1 != null)
                    reader1.Close();
                miConexion.CerrarConexionBd();
            }
        }

        public Salon BuscarSalon(int idSalon)
        {
            //Dado el id del salon se busca en la base de datos y se obtienen los datos del mismo
            //para poder guardarlos en los objetos.
            Salon retorno = new Salon();
            SqlDataReader reader = null;
            Conexion miConexion = new Conexion();
            SqlCommand command = new SqlCommand();
            try
            {

                //Se abre la conexion a base de datos
                miConexion.AbrirConexionBd();

                //Se introducen los datos de la conexion
                command.Connection = miConexion.ObjetoConexion();
                command.CommandType = System.Data.CommandType.Text;
                command.CommandText = "SELECT nombre FROM Salon WHERE id_salon = " + idSalon;
                command.CommandTimeout = 10;

                reader = command.ExecuteReader();

                while (reader.Read())
                {
                    retorno.NumeroSalon = idSalon;
                    retorno.Nombre = reader.GetString(0);
                }

                return retorno;

            }
            catch (SqlException)
            {
                return null;
            }

            catch (Exception)
            {
                return null;
            }

            finally
            {
                if (reader != null)
                    reader.Close();
                miConexion.CerrarConexionBd();
            }
        
        }

        #endregion

        #region Cargar Lista de Practicas dada una Seccion de Teoria

        public List<SeccionMateria> ListaPracticas(List<SeccionMateria> miLista)
        {
            //Como una Materia puede tener distintas practicas, se procede a la busqueda de
            //todas las practicas pertenecientes a una materia, se recibe el id y se trabaja de
            //igual manera que en procesos anteriores.
            foreach (SeccionMateria miSeccion in miLista)
            {
                SqlDataReader reader = null;
                Conexion miConexion = new Conexion();
                SqlCommand command = new SqlCommand();

                try
                {

                    //Se abre la conexion a base de datos
                    miConexion.AbrirConexionBd();

                    //Se introducen los datos de la conexion
                    command.Connection = miConexion.ObjetoConexion();
                    command.CommandType = System.Data.CommandType.Text;
                    command.CommandText = "SELECT id_Practica FROM Practica WHERE fk_SeccionMateria = " + miSeccion.IdSeccionMateria +
                                            "AND statusPractica = 'Activo'";
                    command.CommandTimeout = 10;

                    reader = command.ExecuteReader();

                    List<int> listaIdPractica = new List<int>();

                    while (reader.Read())
                    {
                        listaIdPractica.Add(reader.GetInt32(0));
                    }

                    if (listaIdPractica.Count != 0)
                    {
                        foreach (int idPractica in listaIdPractica)
                        {
                            Practica miPractica = new Practica();
                            miPractica = BuscarPractica(idPractica);
                            miSeccion.ListaPractica.Add(miPractica);
                        }
                    }

                }
                catch (SqlException)
                {
                    return miLista;
                }

                catch (Exception)
                {
                    return miLista;
                }

                finally
                {
                    if (reader != null)
                        reader.Close();
                    miConexion.CerrarConexionBd();
                }
            }

            return miLista;
        }

        #endregion

        #region Cargar Lista de Materias Aprobadas

        public List<Materia> MisMateriasAprobadas(int idEstudiante)
        {
            
            /**
             * Esta funcion se encarga de retornar una lista de materias que fueron aprobadas dadas por el id del estudiante
             * que se recibio por parametro. Cada materia esta llena con toda la informacion ya que se hizo uso de la funcion
             * de BuscarMateria y se le pasa el id que se recibio del query.
             **/
            
            List<Materia> retorno = new List<Materia>();

            Conexion miConexion = new Conexion();
            SqlCommand command = new SqlCommand();
            SqlDataReader reader = null;

            try
            {

                //Se abre la conexion a base de datos
                miConexion.AbrirConexionBd();

                //Se introducen los datos de la conexion
                command.Connection = miConexion.ObjetoConexion();
                command.CommandType = System.Data.CommandType.Text;
                command.CommandText = "SELECT fk_materia FROM HistorialEstudianteMateria WHERE fk_estudiante = " + idEstudiante + " AND notaDefinitiva >9";
                command.CommandTimeout = 10;

                reader = command.ExecuteReader();

                while (reader.Read())
                {

                    Materia miMateria = new Materia();

                    miMateria.Codigo = reader.GetInt32(0);
                    retorno.Add(miMateria);
                }

                for (int contador = 0; contador < retorno.Count(); contador++)
                    retorno[contador] = BuscarMateria(retorno[contador].Codigo);

                return retorno;

            }
            catch (SqlException)
            {
                return null;
            }

            catch (Exception)
            {
                return null;
            }

            finally
            {
                if (reader != null)
                    reader.Close();
                miConexion.CerrarConexionBd();
            }
        }

        #endregion

        #region Cargar Lista de Estudiantes con Horarios Pendientes

        public List<Estudiante> CargarHorariosEstudiantes(int idEscuela)
        {
            
            /**
             * Esta funcion se encarga de recibir el id de una escuela y retorna una lista de todos los estudiantes que
             * pertenecen a dicha escuela, cada estudiante poseera toda la lista de los horarios que tengan,
             * independientemente del estatus que posean los horarios. Aqui se llena toda la informacion que posee
             * el Estudiante.
             **/
            
            List<Estudiante> retorno = new List<Estudiante>();
            Conexion miConexion = new Conexion();
            SqlCommand command = new SqlCommand();
            SqlDataReader reader = null;
            List<int> idEstudiantes = new List<int>();

            try
            {

                //Se abre la conexion a base de datos
                miConexion.AbrirConexionBd();

                //Se introducen los datos de la conexion
                command.Connection = miConexion.ObjetoConexion();
                command.CommandType = System.Data.CommandType.Text;
                command.CommandText = "SELECT S.fk_Estudiante, E.nombre as nombre, E.apellido as Apellido, " +
                                       "E.cedula as Cedula, E.correo as correo, E.noExpediente as expe, " +
                                       " E.sexo as sexo, E.fechaNac as fecha, E.telefono as telef " +
                                       "FROM EscuelaEstudiante S, Estudiante E " +
                                       "WHERE S.fechaFin is NULL " +
                                       "AND S.fk_Estudiante = E.id_estudiante " +
                                       "AND S.fk_Escuela =" + idEscuela;

                command.CommandTimeout = 10;

                reader = command.ExecuteReader();

                while (reader.Read())
                {
                    Estudiante miEstudiante = new Estudiante();
                    miEstudiante.IdEst = reader.GetInt32(0);
                    miEstudiante.Nombre = reader.GetString(1);
                    miEstudiante.Apellido = reader.GetString(2);
                    miEstudiante.Cedula = reader.GetInt32(3);
                    miEstudiante.Correo = reader.GetString(4);
                    miEstudiante.Expediente = reader.GetString(5);
                    miEstudiante.Sexo = reader.GetString(6);
                    miEstudiante.FechaNacimiento = reader.GetDateTime(7);
                    miEstudiante.Telefono = reader.GetString(8);
                    retorno.Add(miEstudiante);
                }

                if (retorno.Count != 0)
                {
                    foreach (Estudiante estudiante in retorno)
                    {
                        estudiante.ListaHorarioPreMatricula = CargarMisHorarios(estudiante.IdEst);
                    }

                }

                return retorno;

            }
            catch (SqlException)
            {
                return null;
            }

            catch (Exception)
            {
                return null;
            }

            finally
            {
                if (reader != null)
                    reader.Close();
                miConexion.CerrarConexionBd();
            }
        }

        #endregion

        #region Carga de Materias Disponibles


        public List<Prelacion> MateriasDisponibles(string nombreEscuela)
        {

            /**
             * Esta funcion me retorna una lista de Prelacion dado el nombre de la escuela, esto quiere decir que, la
             * lista que se retorna viene filtrado por el pensum que se encuentre activo en este momento, cada Prelacion
             * contiene una materia base y una lista de materias que prelan la materia base, esta funcion es utilizada
             * para ser filtrada posteriormente a tal punto que solo quedan las materias que puede ver el estudiante
             * a la hora de crear una Pre Matricula nueva.
             **/

            List<Prelacion> retorno = new List<Prelacion>();
            Conexion miConexion = new Conexion();
            SqlCommand command = new SqlCommand();
            SqlDataReader reader = null;
            int idEscuela = 0;
            int idPensum = 0;

            try
            {

                //Se abre la conexion a base de datos
                miConexion.AbrirConexionBd();

                //Se introducen los datos de la conexion
                command.Connection = miConexion.ObjetoConexion();
                command.CommandType = System.Data.CommandType.Text;
                command.CommandText = "SELECT id_Escuela FROM ESCUELA WHERE nombre =  '" + nombreEscuela + "'";
                command.CommandTimeout = 10;

                reader = command.ExecuteReader();

                while (reader.Read())
                {
                    idEscuela = reader.GetInt32(0);
                }

                idPensum = BuscarPensum(idEscuela);
                retorno = CrearListaPrelacion(idPensum);
                return retorno;

            }
            catch (SqlException)
            {
                return null;
            }

            catch (Exception)
            {
                return null;
            }

            finally
            {
                if (reader != null)
                    reader.Close();
                miConexion.CerrarConexionBd();
            }
        }

        public int BuscarPensum(int idEscuela)
        {
            
            /**
             * Esta funcion se encarga de buscar y retornar el id del pensum que se encuentre activado dado el id de
             * la escuela. Esto nos servira para buscar las materias que se encuentren activadas para el pensum actual.
             **/ 
            
            Conexion miConexion = new Conexion();
            SqlCommand command = new SqlCommand();
            SqlDataReader reader = null;
            Pensum retorno = new Pensum();
            int idPensum = 0;

            try
            {

                //Se abre la conexion a base de datos
                miConexion.AbrirConexionBd();

                //Se introducen los datos de la conexion
                command.Connection = miConexion.ObjetoConexion();
                command.CommandType = System.Data.CommandType.Text;
                command.CommandText = "SELECT id_Pensum FROM Pensum WHERE fk_id_escuela = " + idEscuela + " AND statusPensum = 'ACTIVADO'";
                command.CommandTimeout = 10;

                reader = command.ExecuteReader();

                while (reader.Read())
                {
                    idPensum = reader.GetInt32(0);
                }

                return idPensum;

            }
            catch (SqlException)
            {
                return 0;
            }

            catch (Exception)
            {
                return 0;
            }

            finally
            {
                if (reader != null)
                    reader.Close();
                miConexion.CerrarConexionBd();
            }
        }

        public List<Prelacion> CrearListaPrelacion(int idPensum)
        {
            
            /**
             * Esta funcion se encarga de buscar y retornar una lista de Prelaciones dado el id de Pensum que se recibe
             * por parametro, aqui se crean las distintas Prelaciones y se llenan con la informacion que estas poseen
             * pero no llenan la lista de materias que prelan a la materia ni la informacion de la materia base, esto se
             * hace con unas llamadas a procedimientos que se explican mas abajo. Se implementa el uso de una lista de
             * enteros que guarda el id de las materias, luego se va recorriendo uno a uno la lista de retorno y la lista
             * que contiene los ids de las materias, ambas lista se corresponden 1 a 1 por la posicion, por lo que en cada
             * ciclo de recorrido de la lista toma el id de la materia, manda a buscarla, se retorna un objeto de MAteria
             * con toda la informacion de la materia base y se asigna a la Prelacion, lo mismo ocurre con la lista de las
             * prelaciones, s emanda a buscar la lista de materias que prelan la materia base mandando el id del pensum
             * vigente y el id de la lista de id materias para posteriormente asignarla a la lista de materias prela de
             * la prelacion.
             **/

            List<Prelacion> retorno = new List<Prelacion>();
            Conexion miConexion = new Conexion();
            SqlCommand command = new SqlCommand();
            SqlDataReader reader = null;
            List<int> listaMaterias = new List<int>();


            try
            {

                //Se abre la conexion a base de datos
                miConexion.AbrirConexionBd();

                //Se introducen los datos de la conexion
                command.Connection = miConexion.ObjetoConexion();
                command.CommandType = System.Data.CommandType.Text;
                command.CommandText = "SELECT semestre, numCreditos, fk_id_materia FROM PensumMateria WHERE fk_id_pensum =" + idPensum + " ORDER BY fk_id_materia ASC";
                command.CommandTimeout = 10;

                reader = command.ExecuteReader();

                while (reader.Read())
                {

                    if (!Existe(reader.GetInt32(2), listaMaterias))
                    {
                        Prelacion miPrelacion = new Prelacion();
                        miPrelacion.SemestreMateriaPensum = reader.GetInt32(0);
                        miPrelacion.CreditoMateriaPensum = reader.GetInt32(1);
                        listaMaterias.Add(reader.GetInt32(2));
                        retorno.Add(miPrelacion);
                    }

                }

                for (int contador = 0; contador < listaMaterias.Count; contador++)
                {

                    Materia miMateria = new Materia();
                    miMateria = BuscarMateria(listaMaterias[contador]);
                    retorno[contador].MiMateriaPensum = miMateria;
                    retorno[contador].Prelaciones = BuscarListaPrelacion(idPensum, listaMaterias[contador]);

                }

                return retorno;

            }
            catch (SqlException)
            {
                return null;
            }

            catch (Exception)
            {
                return null;
            }

            finally
            {
                if (reader != null)
                    reader.Close();
                miConexion.CerrarConexionBd();
            }
        }

        public bool Existe(int id, List<int> prelaciones)
        {

            /** 
             * Este procedimiento recibe un id y una lista de enteros, aqui se comprueba la presencia del id en la lista
             * si esto ocurre, se retorna true, de lo contrario se retorna false. Esto se aplica para evitar la duplicacion
             * de objetos en una lista.
             **/

            bool retorno = false;

            foreach (int miPrelacion in prelaciones)
            {

                if (miPrelacion == id)
                    retorno = true;

            }

            return retorno;
        }

        public List<Materia> BuscarListaPrelacion(int idPensum, int idMateria)
        {
            
            /**
             * Esta funcion se encarga de retornar una lista de materias dado el id de un pensum y el id de una materia.
             * Esta lista de materias son las materias que prelan a la materia cuyo id fue recibida por parametro. Aqui
             * se hace uso de la funcion Buscar Materia que dada el id de la materia me trae un objeto de tipo Materia
             * con todos los datos llenos.
             **/
            
            List<Materia> retorno = new List<Materia>();
            Conexion miConexion = new Conexion();
            SqlCommand command = new SqlCommand();
            SqlDataReader reader = null;
            List<int> lista = new List<int>();

            try
            {

                //Se abre la conexion a base de datos
                miConexion.AbrirConexionBd();

                //Se introducen los datos de la conexion
                command.Connection = miConexion.ObjetoConexion();
                command.CommandType = System.Data.CommandType.Text;
                command.CommandText = "SELECT fk_id_materia_prela FROM PensumMateria WHERE fk_id_materia =" + idMateria + " AND fk_id_pensum = " + idPensum + " ORDER BY fk_id_materia_prela ASC";
                command.CommandTimeout = 10;

                reader = command.ExecuteReader();

                while (reader.Read())
                {

                    lista.Add(reader.GetInt32(0));

                }

                if (lista.Count != 0)
                {
                    foreach (int idmateria in lista)
                    {

                        Materia materia = new Materia();
                        materia = BuscarMateria(idmateria);
                        retorno.Add(materia);

                    }
                }
                else
                {
                    lista = null;
                }

                return retorno;

            }
            catch (SqlException)
            {
                return null;
            }

            catch (Exception)
            {
                return null;
            }

            finally
            {
                if (reader != null)
                    reader.Close();
                miConexion.CerrarConexionBd();
            }
        }


        #endregion

        #region Dada una Materia Traeme la Lista de secciones que Tiene

        public List<SeccionMateria> ConsultarSeccionesParaUnaMateria(int idMateria)
        {
            
            /**
             * Esta funcion se encarga de retornar una lista de SeccionMateria con sus respectivos profesores, lista de
             * horarios y los salones, de la materia cuyo idMateria fue recibida por parametro, aqui se valida que
             * estas secciones esten activas para poder ser mostradas al estudiante a la hora de hacer un horario
             **/
            
            List<SeccionMateria> listaSecciones = new List<SeccionMateria>();
            Conexion conex = new Conexion();
            SqlCommand command = new SqlCommand();
            SqlDataReader reader = null;

            List<int> listaGenericaProfesor = new List<int>();
            List<int> listaGenericaMateria = new List<int>();
            try
            {
                conex.AbrirConexionBd();
                command.Connection = conex.ObjetoConexion();
                command.CommandType = System.Data.CommandType.Text;
                command.CommandText = "SELECT S.numeroSeccion as Codigo, S.fk_Profesor as idProfMateria, " +
                                        "S.id_Seccion as idSeccionMateria " +
                                        "FROM SeccionMateria S, PeriodoAcademico P " +
                                        "WHERE S.fk_Materia = " + idMateria + " " +
                                        "AND P.ID_PERIODOACADEMICO = S.fk_periodoAcademico " +
                                        "AND GETDATE() BETWEEN P.fechaInicio AND fechaFin";
                command.CommandTimeout = 10;

                reader = command.ExecuteReader();

                while (reader.Read())
                {
                    SeccionMateria secMat = new SeccionMateria();

                    secMat.Codigo = reader.GetInt32(0);
                    listaGenericaProfesor.Add(reader.GetInt32(1));
                    secMat.IdSeccionMateria = reader.GetInt32(2);
                    listaSecciones.Add(secMat);
                }

                SqlServerHorarioPreMatricula sql = new SqlServerHorarioPreMatricula();

                for (int index = 0; index < listaSecciones.Count(); index++)
                {
                    listaSecciones[index].Profesor = sql.BuscarProfesor(listaGenericaProfesor[index]);
                    listaSecciones[index].HorasClase = sql.BuscarListaHorariosClaseMateria(listaSecciones[index].IdSeccionMateria);
                }
                return listaSecciones;
            }
            catch (SqlException)
            {
                return null;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                if (reader != null)
                    reader.Close();
                conex.CerrarConexionBd();
            }
        }

        #endregion

        #region Eliminar PreMatricula

        public void EliminarPreMatricula(int idPreMatricula)
        {

            /**
             * Esta funcion se encarga de eliminar de la base de datos, en las tablas de EstudianteHorario y HorarioPreMatricula
             * el horario cuyo id fue recibido por parametro, se utilizan 2 variables de "miConexion", "command" y "reader" 
             * para poder ejecutar ambos querys en la misma funcion.
             **/

            Conexion miConexion = new Conexion();
            SqlCommand command = new SqlCommand();
            SqlDataReader reader = null;

            Conexion miConexion1 = new Conexion();
            SqlCommand command1 = new SqlCommand();
            SqlDataReader reader1 = null;

            try
            {

                //Se abre la conexion a base de datos
                miConexion.AbrirConexionBd();

                //Se introducen los datos de la conexion
                command.Connection = miConexion.ObjetoConexion();
                command.CommandType = System.Data.CommandType.Text;
                command.CommandText = "DELETE FROM EstudianteHorario WHERE fk_HorarioPreMatricula = " + idPreMatricula;
                command.CommandTimeout = 10;

                reader = command.ExecuteReader();

                //Se abre la conexion a base de datos
                miConexion1.AbrirConexionBd();

                //Se introducen los datos de la conexion
                command1.Connection = miConexion1.ObjetoConexion();
                command1.CommandType = System.Data.CommandType.Text;
                command1.CommandText = "DELETE FROM HorarioPreMatricula WHERE id_HorarioPreMatricula = " + idPreMatricula;
                command1.CommandTimeout = 10;

                reader1 = command1.ExecuteReader();

            }
            catch (SqlException)
            {
            }

            catch (Exception)
            {
            }

            finally
            {
                miConexion.CerrarConexionBd();
                miConexion1.CerrarConexionBd();
            }

        }

        #endregion

        #region Cambio de Estatus de Activo a Historial

        public bool CambioEstatusHorario(int idHorario)
        {
            
            /**
             * Esta funcion se encarga de cambiarle el estatus cuyo idHorario es recibido por parametro, a este horario 
             * se le cambiara el estatus de Activo a Historial, esto sucede cuando se finaliza un periodo academico y 
             * todos los horarios Activos pasan al estatus de Historial. Para la ejecucion de esta funcion se crean las
             * variables necesarias para abrir la conexion a base de datos, estas variables son el "reader", "miConexion" 
             * y "command". Esta funcion retorna un bool que sera True si el proceso se ejecuta con exito, de lo contrario
             * retornara False.
             **/

            SqlDataReader reader = null;
            Conexion miConexion = new Conexion();
            SqlCommand command = new SqlCommand();

            try
            {

                //Se abre la conexion a base de datos
                miConexion.AbrirConexionBd();

                //Se introducen los datos de la conexion
                command.Connection = miConexion.ObjetoConexion();
                command.CommandType = System.Data.CommandType.Text;
                command.CommandText = "UPDATE HorarioPreMatricula SET estatus = 'Historial' WHERE id_HorarioPreMatricula = " + idHorario;
                command.CommandTimeout = 10;

                reader = command.ExecuteReader();

                return true;

            }
            catch (SqlException)
            {
                return false;
            }

            catch (Exception)
            {
                return false;
            }

            finally
            {
                if (reader != null)
                    reader.Close();
                miConexion.CerrarConexionBd();
            }

        }

        #endregion

        #region Aprobar Horario de Estudiante

        public bool AprobarHorario(int idHorario)
        {

            SqlDataReader reader = null;
            Conexion miConexion = new Conexion();
            SqlCommand command = new SqlCommand();

            try
            {

                //Se abre la conexion a base de datos
                miConexion.AbrirConexionBd();

                //Se introducen los datos de la conexion
                command.Connection = miConexion.ObjetoConexion();
                command.CommandType = System.Data.CommandType.Text;
                command.CommandText = "UPDATE HorarioPreMatricula set estatus = 'Activo' Where id_HorarioPreMatricula = "+ idHorario;
                command.CommandTimeout = 10;

                reader = command.ExecuteReader();

                return true;

            }
            catch (SqlException)
            {
                return false;
            }

            catch (Exception)
            {
                return false;
            }

            finally
            {
                if (reader != null)
                    reader.Close();
                miConexion.CerrarConexionBd();
            }
        
        }

        #endregion

        #region Carga de Estudiante dado su id

        public Estudiante BuscarEstudiante(int idEstudiante)
        {

            Estudiante retorno = new Estudiante();
            retorno.IdEst = idEstudiante;

            SqlDataReader reader = null;
            Conexion miConexion = new Conexion();
            SqlCommand command = new SqlCommand();

            try
            {

                //Se abre la conexion a base de datos
                miConexion.AbrirConexionBd();

                //Se introducen los datos de la conexion
                command.Connection = miConexion.ObjetoConexion();
                command.CommandType = System.Data.CommandType.Text;
                command.CommandText = "SELECT  u.cedula, u.noExpediente, u.nombre, u.apellido, u.fechaNac, u.sexo,"+
                                      "u.telefono, u.correo FROM ESTUDIANTE u WHERE u.id_estudiante = "+ idEstudiante;
                command.CommandTimeout = 10;

                reader = command.ExecuteReader();

                while (reader.Read())
                {
                    retorno.Cedula = reader.GetInt32(0);
                    retorno.Expediente = reader.GetString(1);
                    retorno.Nombre = reader.GetString(2);
                    retorno.Apellido = reader.GetString(3);
                    retorno.FechaNacimiento = reader.GetDateTime(4);
                    retorno.Sexo = reader.GetString(5);
                    retorno.Telefono = reader.GetString(6);
                    retorno.Correo = reader.GetString(7);
                }

                return retorno;

            }
            catch (SqlException)
            {
                return null;
            }

            catch (Exception)
            {
                return null;
            }

            finally
            {
                if (reader != null)
                    reader.Close();
                miConexion.CerrarConexionBd();
            }
        }

        #endregion

    }
}